/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package mpinventory;

import java.sql.*;
import java.util.List;
import java.util.ArrayList;
import java.util.logging.Level;
import java.util.logging.Logger;
import javax.swing.JOptionPane;
/**
 *
 * @author user
 */
public class InstallationQuery {
    private Connection conn;
    public Installation installation;
    public InstallationQuery(Connection conn){
        this.conn=conn;
    }
    public List<Item> initializeItems(){
        List<Item> items=new ArrayList<Item>();
        try{
            Statement stmt=conn.createStatement();
            String query="select * from inventoryitemdata ORDER BY itemName ASC";
            ResultSet rs=stmt.executeQuery(query);
            while(rs.next()){
                Item item=new Item();
                item.setID(rs.getInt("itemID"));
                item.setQuantity(rs.getFloat("itemQuantity"));
                item.setQuantityUnit(rs.getString("itemQuantityUnit"));
                item.setDescription(rs.getString("itemName"));
                item.setUnitPrice(rs.getFloat("itemPrice"));
                items.add(item);
            }
        }
        catch(SQLException ex){
            Logger.getLogger(InstallationQuery.class.getName()).log(Level.SEVERE, null, ex);
        }
        return items;
    }
    public boolean canAddInstallation(String brandName, String brandModel){
        String query="select brandName, brandModel FROM airconinstallationdata where brandName = ? AND brandModel = ?";
        try{
            PreparedStatement prest=conn.prepareStatement(query);
            prest.setString(1, brandName);
            prest.setString(2, brandModel);
            ResultSet rs=prest.executeQuery();
            while(rs.next())
                return false;
        }
        catch(SQLException ex){
            Logger.getLogger(InstallationQuery.class.getName()).log(Level.SEVERE, null, ex);
        }
        return true;
    }
    public void addInstallation(int installationID, String brandName, String brandModel, float horsePower, String brandDescription, List<Float> installationQuantity, List<String> installationUnit, List<Integer> itemID, List<String> itemName){
        String query="insert into airconinstallationdata(installationID, brandName, brandModel, horsepower, brandDescription) values(?,?,?,?,?)";
        try{
            PreparedStatement prest=conn.prepareStatement(query);
            prest.setInt(1, installationID);
            prest.setString(2, brandName);
            prest.setString(3, brandModel);
            prest.setFloat(4, horsePower);
            prest.setString(5, brandDescription);
            prest.executeUpdate();
            JOptionPane.showMessageDialog(null, "Successfully added installation details for " + brandName + " " + brandModel + " to the database!");
        }
        catch(SQLException ex){
            Logger.getLogger(InstallationQuery.class.getName()).log(Level.SEVERE, null, ex);
        }
        query="insert into airconinstallation(ID, installationQuantity, installationUnit, itemID, itemName) values(?,?,?,?,?)";
        int i=0;
        int ctr=0;
        int airconInstallationID=setAirconInstallationID();
        while(!itemID.isEmpty()){
            airconInstallationID+=ctr;
            try{
                PreparedStatement prest=conn.prepareStatement(query);
                prest.setInt(1, airconInstallationID);
                prest.setFloat(2, installationQuantity.get(i));
                prest.setString(3, installationUnit.get(i));
                prest.setInt(4, itemID.get(i));
                prest.setString(5, itemName.get(i));
                prest.executeUpdate();
                String query2="insert into airconinstallationwe(ID, installationID) values(?,?)";
                try{
                    PreparedStatement prest2=conn.prepareStatement(query2);
                    prest2.setInt(1, airconInstallationID);
                    prest2.setInt(2, installationID);
                    prest2.executeUpdate();
                }
                catch(SQLException ex){
                    Logger.getLogger(InstallationQuery.class.getName()).log(Level.SEVERE, null, ex);
                }
                installationQuantity.remove(i);
                installationUnit.remove(i);
                itemID.remove(i);
                itemName.remove(i);
                ctr++;
            }
            catch(SQLException ex){
                Logger.getLogger(InstallationQuery.class.getName()).log(Level.SEVERE, null, ex);
            }
        }
    }
    public List<Installation> initializeInstallations(){
        List<Installation> installations=new ArrayList<Installation>();
        List<Integer> IDs=new ArrayList<Integer>();
        List<Integer> itemIDs=new ArrayList<Integer>();
        List<String> descriptions=new ArrayList<String>();
        List<Float> quantities=new ArrayList<Float>();
        List<String> units=new ArrayList<String>();
        List<Float> prices=new ArrayList<Float>();
        try{
            Statement stmt=conn.createStatement();
            String query="SELECT * FROM airconinstallation NATURAL JOIN airconinstallationwe NATURAL JOIN airconinstallationdata NATURAL JOIN inventoryitemdata ORDER BY brandName ASC";
            ResultSet rs=stmt.executeQuery(query);
            int installationID=0;
            Installation installation=new Installation();
            int i=0;
            while(rs.next()){
                if(installationID!=rs.getInt("installationID")){
                    if(i!=0){
                        installation.setIDs(IDs);
                        installation.setItemIDs(itemIDs);
                        installation.setItemNames(descriptions);
                        installation.setQuantities(quantities);
                        installation.setUnits(units);
                        installation.setPrices(prices);
                        installations.add(installation);
                    }
                    installation=new Installation();
                    installationID=rs.getInt("installationID");
                    installation.setInstallationID(installationID);
                    installation.setBrand(rs.getString("brandName"));
                    installation.setModel(rs.getString("brandModel"));
                    installation.setDescription(rs.getString("brandDescription"));
                    installation.setHorsePower(rs.getFloat("horsepower"));
                    IDs.add(rs.getInt("ID"));
                    itemIDs.add(rs.getInt("itemID"));
                    descriptions.add(rs.getString("itemName"));
                    quantities.add(rs.getFloat("installationQuantity"));
                    units.add(rs.getString("installationUnit"));
                    prices.add(rs.getFloat("itemPrice"));
                    i=1;
                }
                else{
                    IDs.add(rs.getInt("ID"));
                    itemIDs.add(rs.getInt("itemID"));
                    descriptions.add(rs.getString("itemName"));
                    quantities.add(rs.getFloat("installationQuantity"));
                    units.add(rs.getString("installationUnit"));
                    prices.add(rs.getFloat("itemPrice"));
                }
            }
            installation.setIDs(IDs);
            installation.setItemIDs(itemIDs);
            installation.setItemNames(descriptions);
            installation.setQuantities(quantities);
            installation.setUnits(units);
            installation.setPrices(prices);
            installations.add(installation);
        }
        catch(SQLException ex){
            Logger.getLogger(InstallationQuery.class.getName()).log(Level.SEVERE, null, ex);
        }
        return installations;
    }
    public void editInstallation(int installationID, String brandName, String brandModel, float horsePower, String brandDescription, List<Float> installationQuantity, List<String> installationUnit, List<Integer> itemID, List<String> itemName, List<Integer> origID){
        String query="delete from airconinstallationwe where ID=? AND installationID=?";
        int i=0;
        while(i!=origID.size())
            try{
                PreparedStatement prest=conn.prepareStatement(query);
                prest.setInt(1, origID.get(i));
                prest.setInt(2, installationID);
                prest.executeUpdate();
                i++;
            }catch(SQLException ex){}
        query="delete from airconinstallation where ID=?";
        i=0;
        while(i!=origID.size())
            try{
                PreparedStatement prest=conn.prepareStatement(query);
                prest.setInt(1, origID.get(i));
                prest.executeUpdate();
                i++;
            }catch(SQLException ex){}

        query="update airconinstallationdata set brandName=?, brandModel=?, horsepower=?, brandDescription=? where installationID=?";
        try{
            PreparedStatement prest=conn.prepareStatement(query);
            prest.setString(1, brandName);
            prest.setString(2, brandModel);
            prest.setFloat(3, horsePower);
            prest.setString(4, brandDescription);
            prest.setInt(5, installationID);
            prest.executeUpdate();
            JOptionPane.showMessageDialog(null, "Successfully edited installation details for " + brandName + " " + brandModel + "!");
        }
        catch(SQLException ex){
            Logger.getLogger(InstallationQuery.class.getName()).log(Level.SEVERE, null, ex);
        }
        query="insert into airconinstallation(ID, installationQuantity, installationUnit, itemID, itemName) values(?,?,?,?,?)";
        i=0;
        int ctr=0;
        int airconInstallationID=setAirconInstallationID();
        while(!itemID.isEmpty()){
            airconInstallationID+=ctr;
            try{
                PreparedStatement prest=conn.prepareStatement(query);
                prest.setInt(1, airconInstallationID);
                prest.setFloat(2, installationQuantity.get(i));
                prest.setString(3, installationUnit.get(i));
                prest.setInt(4, itemID.get(i));
                prest.setString(5, itemName.get(i));
                prest.executeUpdate();
                String query2="insert into airconinstallationwe(ID, installationID) values(?,?)";
                try{
                    PreparedStatement prest2=conn.prepareStatement(query2);
                    prest2.setInt(1, airconInstallationID);
                    prest2.setInt(2, installationID);
                    prest2.executeUpdate();
                }
                catch(SQLException ex){
                    Logger.getLogger(InstallationQuery.class.getName()).log(Level.SEVERE, null, ex);
                }
                installationQuantity.remove(i);
                installationUnit.remove(i);
                itemID.remove(i);
                itemName.remove(i);
                ctr++;
            }
            catch(SQLException ex){
                Logger.getLogger(InstallationQuery.class.getName()).log(Level.SEVERE, null, ex);
            }
        }
    }
    public List<Installation> search(String brand){
        List<Installation> installations=new ArrayList<Installation>();
        List<Integer> IDs=new ArrayList<Integer>();
        List<Integer> itemIDs=new ArrayList<Integer>();
        List<String> descriptions=new ArrayList<String>();
        List<Float> quantities=new ArrayList<Float>();
        List<String> units=new ArrayList<String>();
        List<Float> prices=new ArrayList<Float>();
        try{
            String query="SELECT * FROM airconinstallation NATURAL JOIN airconinstallationwe NATURAL JOIN airconinstallationdata NATURAL JOIN inventoryitemdata where brandName=? ORDER BY brandModel ASC";
            PreparedStatement prest=conn.prepareStatement(query);
            prest.setString(1, brand);
            ResultSet rs=prest.executeQuery();
            int installationID=0;
            Installation installation=new Installation();
            int i=0;
            while(rs.next()){
                if(installationID!=rs.getInt("installationID")){
                    if(i!=0){
                        installation.setIDs(IDs);
                        installation.setItemIDs(itemIDs);
                        installation.setItemNames(descriptions);
                        installation.setQuantities(quantities);
                        installation.setUnits(units);
                        installation.setPrices(prices);
                        installations.add(installation);
                    }
                    installation=new Installation();
                    installationID=rs.getInt("installationID");
                    installation.setInstallationID(installationID);
                    installation.setBrand(rs.getString("brandName"));
                    installation.setModel(rs.getString("brandModel"));
                    installation.setDescription(rs.getString("brandDescription"));
                    installation.setHorsePower(rs.getFloat("horsepower"));
                    IDs.add(rs.getInt("ID"));
                    itemIDs.add(rs.getInt("itemID"));
                    descriptions.add(rs.getString("itemName"));
                    quantities.add(rs.getFloat("installationQuantity"));
                    units.add(rs.getString("installationUnit"));
                    prices.add(rs.getFloat("itemPrice"));
                    i=1;
                }
                else{
                    IDs.add(rs.getInt("ID"));
                    itemIDs.add(rs.getInt("itemID"));
                    descriptions.add(rs.getString("itemName"));
                    quantities.add(rs.getFloat("installationQuantity"));
                    units.add(rs.getString("installationUnit"));
                    prices.add(rs.getFloat("itemPrice"));
                }
            }
            installation.setIDs(IDs);
            installation.setItemIDs(itemIDs);
            installation.setItemNames(descriptions);
            installation.setQuantities(quantities);
            installation.setUnits(units);
            installation.setPrices(prices);
            installations.add(installation);
        }
        catch(SQLException ex){
            Logger.getLogger(InstallationQuery.class.getName()).log(Level.SEVERE, null, ex);
        }
        return installations;
    }
    public Installation search(String brand, String model){
        Installation installation=new Installation();
        List<Integer> IDs=new ArrayList<Integer>();
        List<Integer> itemIDs=new ArrayList<Integer>();
        List<String> descriptions=new ArrayList<String>();
        List<Float> quantities=new ArrayList<Float>();
        List<String> units=new ArrayList<String>();
        List<Float> prices=new ArrayList<Float>();
        try{
            String query="SELECT * FROM airconinstallation NATURAL JOIN airconinstallationwe NATURAL JOIN airconinstallationdata NATURAL JOIN inventoryitemdata where brandName=? AND brandModel=?";
            PreparedStatement prest=conn.prepareStatement(query);
            prest.setString(1, brand);
            prest.setString(2, model);
            ResultSet rs=prest.executeQuery();
            while(rs.next()){
                installation.setInstallationID(rs.getInt("installationID"));
                installation.setBrand(rs.getString("brandName"));
                installation.setModel(rs.getString("brandModel"));
                installation.setDescription(rs.getString("brandDescription"));
                installation.setHorsePower(rs.getFloat("horsepower"));
                IDs.add(rs.getInt("ID"));
                itemIDs.add(rs.getInt("itemID"));
                descriptions.add(rs.getString("itemName"));
                quantities.add(rs.getFloat("installationQuantity"));
                units.add(rs.getString("installationUnit"));
                prices.add(rs.getFloat("itemPrice"));
            }
            installation.setIDs(IDs);
            installation.setItemIDs(itemIDs);
            installation.setItemNames(descriptions);
            installation.setQuantities(quantities);
            installation.setUnits(units);
            installation.setPrices(prices);
        }
        catch(SQLException ex){
            Logger.getLogger(InstallationQuery.class.getName()).log(Level.SEVERE, null, ex);
        }
        return installation;
    }
    public int setID(){
        try{
            Statement stmt=conn.createStatement();
            String query="select max(installationID) as maxID from airconinstallationdata";
            ResultSet rs=stmt.executeQuery(query);
            while(rs.next())
                return rs.getInt("maxID")+1;
        }
        catch(SQLException ex){
            Logger.getLogger(InstallationQuery.class.getName()).log(Level.SEVERE, null, ex);
        }
        return 0;
    }
    public int setAirconInstallationID(){
        try{
            Statement stmt=conn.createStatement();
            String query="select max(ID) as maxID from airconinstallation";
            ResultSet rs=stmt.executeQuery(query);
            while(rs.next())
                return rs.getInt("maxID")+1;
        }
        catch(SQLException ex){
            Logger.getLogger(InstallationQuery.class.getName()).log(Level.SEVERE, null, ex);
        }
        return 0;
    }
}
